<?php

namespace app\admin\service;


use app\admin\model\DeviceUpstreamLog;

/**
 * 设备上行日志管理-服务类
 * Class DeviceUpstreamLogService
 * @package app\admin\service
 */
class DeviceUpstreamLogService extends BaseService
{
    /**
     * 构造函数
     */
    public function __construct()
    {
        $this->model = new DeviceUpstreamLog();
    }

    /**
     * 获取数据列表
     */
    public function getList()
    {
        $param = request()->param();

        // 查询条件
        $map = [];
        $sort = 'log.id desc';
        $is_sql = 0;

        if (!empty($param['sort'])) {
            $sort = $param['sort'];
        }

        if (!empty($param['imei'])) {
            $map[] = ['log.imei', 'like', '%' . $param['imei'] . '%'];
        }

        if (!empty($param['command'])) {
            $map[] = ['command', 'like', '%' . $param['command'] . '%'];
        }

        if (!empty($param['kw'])) {
            $map[] = ['stu.name', 'like', '%' . $param['kw'] . '%'];
        }

        if (!empty($param['status'])) {
            $map[] = ['log.status', '=', $param['status']];
        }

        if (!empty($param['start_time'])) {
            $map[] = ['log.create_time', '>=', strtotime($param['start_time'])];
        }

        if (!empty($param['end_time'])) {
            $map[] = ['log.create_time', '<=', strtotime($param['end_time'])];
        }

        //获取数据总数
        $count = $this->model->alias('log')
            ->leftJoin('student stu','log.imei = stu.imei')
            ->leftJoin('schoole sch','sch.id = stu.schoole_id')
            ->leftJoin('grade','grade.id = stu.grade_id')
            ->leftJoin('class','class.id = stu.class_id')
            ->leftJoin('city p','sch.province_code = p.citycode')//省
            ->leftJoin('city c','sch.city_code = c.citycode')//市
            ->leftJoin('city d','sch.district_code = d.citycode')//区
            ->leftJoin('city s','sch.streets_code = s.citycode')//街道
            ->where($map)
            ->count();
        // 打印SQL
        if ($is_sql) {
            echo $this->model->getLastSql();
        }
        $list = [];
        if ($count > 0) {
            $field = [
                'log.*',
                'stu.name' => 'stu_name',
                'stu.student_no',
                'sch.title' => 'school_name',
                'grade.title' => 'grade_name',
                'class.title' => 'class_name',
                'p.name' => 'province_name',
                'c.name' => 'city_name',
                'd.name' => 'district_name',
                's.name' => 'streets_name',
            ];
            $list = $this->model->alias('log')
                ->leftJoin('student stu','log.imei = stu.imei')
                ->leftJoin('schoole sch','sch.id = stu.schoole_id')
                ->leftJoin('grade','grade.id = stu.grade_id')
                ->leftJoin('class','class.id = stu.class_id')
                ->leftJoin('city p','sch.province_code = p.citycode')//省
                ->leftJoin('city c','sch.city_code = c.citycode')//市
                ->leftJoin('city d','sch.district_code = d.citycode')//区
                ->leftJoin('city s','sch.streets_code = s.citycode')//街道
                ->where($map)
                ->order($sort)
                ->page(PAGE, PERPAGE)
                ->field($field)
                ->select();
        }

        //返回结果
        return array(
            "msg" => '操作成功',
            "code" => 0,
            "data" => $list,
            "count" => $count,
        );

    }


    public function delete()
    {
        // 参数
        $param = request()->param();
        // 记录ID
        $ids = getter($param, "id");
        if (empty($ids)) {
            return message("记录ID不能为空", false);
        }
        $ids = (array)$ids;

        $result = DeviceUpstreamLog::destroy($ids);
        if (!$result) {
            return message("删除失败", false);
        }
        return message("删除成功");
    }


    /**
     * 导出Excel
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function exportExcel()
    {
        // 获取导出的数据源
        $res = $this->getList();
        $list = $res['data'];

        // 实例化PHPExcel类
        $objPHPExcel = new \PHPExcel();
        // 激活当前的sheet表
        $activeSheet = $objPHPExcel->setActiveSheetIndex(0);
        //5.设置表格头（即excel表格的第一行）
        $activeSheet->setCellValue('A1', '姓名')
            ->setCellValue('B1', 'IMEI')
            ->setCellValue('C1', '省市区')
            ->setCellValue('D1', '街道')
            ->setCellValue('E1', '学号')
            ->setCellValue('F1', '所属学校')
            ->setCellValue('G1', '年级')
            ->setCellValue('H1', '班级')
            ->setCellValue('I1', '指令')
            ->setCellValue('J1', '上行数据')
            ->setCellValue('K1', '返回数据')
            ->setCellValue('L1', '状态')
            ->setCellValue('M1', '上报时间');

        //设置列水平居左
        $activeSheet->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $activeSheet->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $activeSheet->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $activeSheet->getStyle('D')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $activeSheet->getStyle('E')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $activeSheet->getStyle('F')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $activeSheet->getStyle('I')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        // 设置单元格宽度
        $activeSheet->getColumnDimension('A')->setWidth(10);
        $activeSheet->getColumnDimension('B')->setWidth(20);
        $activeSheet->getColumnDimension('C')->setWidth(20);
        $activeSheet->getColumnDimension('D')->setWidth(20);
        $activeSheet->getColumnDimension('E')->setWidth(20);
        $activeSheet->getColumnDimension('F')->setWidth(20);
        $activeSheet->getColumnDimension('G')->setWidth(20);
        $activeSheet->getColumnDimension('H')->setWidth(20);
        $activeSheet->getColumnDimension('J')->setWidth(20);
        $activeSheet->getColumnDimension('K')->setWidth(20);
        $activeSheet->getColumnDimension('M')->setWidth(20);

        // 循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($list); $i++) {
            $activeSheet->setCellValue('A' . ($i + 2), $list[$i]['stu_name']);
            $activeSheet->setCellValue('B' . ($i + 2), $list[$i]['imei']);
            $pcd = $list[$i]['province_name'] . $list[$i]['city_name'] . $list[$i]['district_name'];
            $activeSheet->setCellValue('C' . ($i + 2), $pcd);
            $activeSheet->setCellValue('D' . ($i + 2), $list[$i]['streets_name']);
            $activeSheet->setCellValue('E' . ($i + 2), $list[$i]['student_no']);
            $activeSheet->setCellValue('F' . ($i + 2), $list[$i]['school_name']);
            $activeSheet->setCellValue('G' . ($i + 2), $list[$i]['grade_name']);
            $activeSheet->setCellValue('H' . ($i + 2), $list[$i]['class_name']);
            $activeSheet->setCellValue('I' . ($i + 2), $list[$i]['command']);
            $activeSheet->setCellValue('J' . ($i + 2), $list[$i]['up_data']);
            $activeSheet->setCellValue('K' . ($i + 2), $list[$i]['return_data']);
            $activeSheet->setCellValue('L' . ($i + 2), $list[$i]['status'] == 1 ? '正常' : '异常');
            $activeSheet->setCellValue('M' . ($i + 2), $list[$i]['create_time']);
        }
        // 设置保存的Excel表格名称
        $filename = '上行日志_' . date('YmdHis', time()) . ".xlsx";
        // 设置当前激活的sheet表格名称
        $activeSheet->setTitle('上行日志');
        // 保存本地文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $savePath = UPLOAD_TEMP_PATH . "/" . $filename;
        $objWriter->save($savePath);

        // 文件地址
        $filePath = get_image_url(str_replace(PUBLIC_PATH, "", $savePath));
        return message("操作成功", true, $filePath);
    }
}

